﻿CREATE FUNCTION [dbo].[fn_HousekeepingFromClause]
(
	@Table sysname
)
RETURNS VARCHAR(MAX)
AS
BEGIN
	DECLARE @FromClause         VARCHAR(MAX)
	DECLARE @ID                 INT
	DECLARE @ColumnName         sysname
	DECLARE @PrimaryKeyColumns  TABLE (
	            ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
	            ColumnName sysname
	        )
	
	-- get a list of columns in the primary key
	INSERT INTO @PrimaryKeyColumns
	  (
	    ColumnName
	  )
	SELECT ColumnName
	FROM   dbo.fn_PrimaryKeyColumns(@Table)
	
	
	-- build from clause for use in creating the housekeeping trigger
	SET @FromClause = NULL
	
	SELECT @ID = MIN(ID)
	FROM   @PrimaryKeyColumns
	
	WHILE @ID IS NOT NULL
	BEGIN
	    SELECT @ColumnName = ColumnName
	    FROM   @PrimaryKeyColumns
	    WHERE  ID = @ID
	    
	    IF @FromClause IS NULL
	        SET @FromClause = 't.' + @ColumnName + ' = ' + 'i.' + @ColumnName
	    ELSE
	        SET @FromClause = @FromClause + ' AND ' + 't.' + @ColumnName + ' = ' + 'i.' + @ColumnName
	    
	    SELECT @ID = MIN(ID)
	    FROM   @PrimaryKeyColumns
	    WHERE  ID > @ID
	END
	
	SET @FromClause = 'FROM ' + @Table + ' t INNER JOIN inserted i ON ' + @FromClause
	
	RETURN @FromClause
END